Replication databases should have authorized db_owner role members. The replication monitor role should have authorized members.

Overview

Finding ID	Version	Rule ID	IA Controls	Severity
V-15178	DM6070-SQLServer9	SV-23855r2_rule	ECLP-1	Medium

Description
Role privileges required by replication include full privileges to the databases with replicated objects. Restrict replication database db_owner role memberships and the system distribution database replmonitor database role membership to authorized replication agent accounts that require access to the database. Unauthorized access can provide unintentional or malicious users greater opportunity to exploit replication access.

Description

Role privileges required by replication include full privileges to the databases with replicated objects. Restrict replication database db_owner role memberships and the system distribution database replmonitor database role membership to authorized replication agent accounts that require access to the database. Unauthorized access can provide unintentional or malicious users greater opportunity to exploit replication access.

STIG	Date
Microsoft SQL Server 2005 Instance Security Technical Implementation Guide	2015-06-16

Details

Check Text ( C-20516r2_chk )
From the query prompt: SELECT COUNT(name) FROM [master].sys.databases WHERE name = 'distribution' AND state = 0 If count = 0, the distribution database does not exist and this check is Not a Finding. From the query prompt: USE distribution EXEC SP_HELPROLEMEMBER 'replmonitor' View list of databases participating in replication: EXEC SP_HELPREPLICATIONDBOPTION For each replication database: USE [database name] EXEC SP_HELPROLEMEMBER 'db_owner' If any role members listed are not authorized for replication access in the System Security Plan, this is a Finding.

Check Text ( C-20516r2_chk )

From the query prompt:

SELECT COUNT(name)
FROM [master].sys.databases
WHERE name = 'distribution'
AND state = 0

If count = 0, the distribution database does not exist and this check is Not a Finding.

From the query prompt:

USE distribution
EXEC SP_HELPROLEMEMBER 'replmonitor'

View list of databases participating in replication:

EXEC SP_HELPREPLICATIONDBOPTION

For each replication database:

USE [database name]
EXEC SP_HELPROLEMEMBER 'db_owner'

If any role members listed are not authorized for replication access in the System Security Plan, this is a Finding.

Fix Text (F-14829r1_fix)
Revoke role membership for unauthorized accounts granted replication role memberships: USE [database name] EXEC SP_DROPROLEMEMBER '[replmonitor or db_owner]' FROM '[account name]'